/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dal;

import connection.ConnectionDB;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.TourBooking;

/**
 *
 * @author Administrator
 */
public class TourBookingDAL {
   private ConnectionDB conn = new ConnectionDB();
    private CallableStatement cs;
    private ResultSet rs;

    public List<TourBooking> getListTourBooking() {
        List<TourBooking> lTourBooking = new ArrayList<TourBooking>();
        TourBooking tb;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListOrderTour()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                tb = new TourBooking();
                tb.setTourBookingID(rs.getInt("TourBookingID"));
                tb.setCustomerID(rs.getInt("CustomerID"));
                tb.setPackageTourID(rs.getInt("PackageTourID"));
                tb.setQuantity(rs.getInt("Quantity"));
                tb.setPrice(rs.getFloat("TotalPrice"));
                tb.setAvailable(rs.getBoolean("Available"));
                lTourBooking.add(tb);

            }
            return lTourBooking;
        } catch (SQLException ex) {
            Logger.getLogger(TourBookingDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

    public List<TourBooking> getSearchTourBookingByUser(String username) {
        List<TourBooking> lTourBooking = new ArrayList<TourBooking>();
        TourBooking tb;
        try {
            cs = conn.createConnection().prepareCall("{call sp_searchBookingByUserName(?)}");
            cs.setString("username",username);
            rs = cs.executeQuery();
            while (rs.next()) {
                tb = new TourBooking();
                tb.setTourBookingID(rs.getInt("TourBookingID"));
                tb.setCustomerID(rs.getInt("CustomerID"));
                tb.setPackageTourID(rs.getInt("PackageTourID"));
                tb.setQuantity(rs.getInt("Quantity"));
                tb.setPrice(rs.getFloat("TotalPrice"));
                tb.setAvailable(rs.getBoolean("Available"));
                lTourBooking.add(tb);

            }
            return lTourBooking;
        } catch (SQLException ex) {
            Logger.getLogger(TourBookingDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }



    public String getUserName(int id) {
         String name = null;
        try {

            cs = conn.createConnection().prepareCall("{call sp_getUserName(?)}");
            cs.setInt("iduser", id);
            rs = cs.executeQuery();
            while (rs.next()) {
                name = rs.getString("UserName");

            }
            return name;
        } catch (SQLException ex) {
            Logger.getLogger(TourBookingDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

     public String getPackageName(int id) {
         String name = null;
        try {

            cs = conn.createConnection().prepareCall("{call sp_getPackageName(?)}");
            cs.setInt("idpackagetour", id);
            rs = cs.executeQuery();
            while (rs.next()) {
                name = rs.getString("PackageTourName");

            }
            return name;
        } catch (SQLException ex) {
            Logger.getLogger(TourBookingDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }


     public boolean updateStatusTourBookigng(int idtour,int status){
        try {
            cs = conn.createConnection().prepareCall("{call sp_UpdateStatusTourBooking(?,?)}");
            cs.setInt("id", idtour);
            cs.setInt("status", status);
            if (cs.executeUpdate() == 1) {

                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TourBookingDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

       return false;

     }


    
}
